﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data.Common;

namespace LP580.CG
{
    public class DBase
    {
        private SqlConnection Conn;
        string connectionString;
        private bool CreateLink()
        {
            if (Conn == null)
            {
                if (connectionString == null)
                {
                    connectionString = ConfigurationManager.ConnectionStrings["SiteDBstring"].ToString();
                }
                Conn = new SqlConnection();
            }

            if (Conn.State == ConnectionState.Closed)
            {
                try
                {
                    Conn.ConnectionString = connectionString;
                    Conn.Open();
                    //OpenNum++;
                }
                catch (Exception e)
                {
                    //message = e.Message;
                    return false;
                }
            }
            return true;
        }
        public DBase()
        {
            connectionString = ConfigurationManager.ConnectionStrings["SiteDBstring"].ToString();
            this.Conn = new SqlConnection(connectionString);
            this.Conn.Open();
        }

        public void CloseConn()
        {
            if ((this.Conn != null) && (this.Conn.State.ToString() != "Closed"))
            {
                this.Conn.Close();
                this.Conn = null;
            }
        }
        public bool ExecSql(string sql)
        {
            SqlCommand command = new SqlCommand(sql, Conn);
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 执行Sql语句，执行完成会自动关闭数据库链接
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>返回数据值</returns>
        public object ExecuteScalar(string sql)
        {
            if (!CreateLink())
            {
                throw new Exception("数据库链接失败！");
            }

            SqlCommand cmd = new SqlCommand(sql, Conn);
            object d = (object)cmd.ExecuteScalar();

            CloseConn();
            return d;
        }
        public IDataReader ExecuteReader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataReader dr = cmd.ExecuteReader();

            return dr;
        }
        /// <summary>
        /// 执行Sql语句，返回DataSet对象。执行完成后会自动关闭数据库链接
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>DataSet对象</returns>
        public DataSet ExecuteDataSet(string sql)
        {
            //SaveExecSql(sql);
            //if (!CreateLink())
            //{
            //    throw new Exception("数据库链接失败！");
            //}
            //Console.WriteLine(sql);

            SqlDataAdapter da = new SqlDataAdapter(sql, Conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            CloseConn();
            return ds;
        }


        /// <summary>
        /// 执行Sql语句，返回DataTable数组对象。执行完成后会自动关闭数据库链接
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>DataTable数组</returns>
        public DataTable[] Execute(string sql)
        {
            //SaveExecSql(sql);
            DataTable[] dts = DataReaderToDataTables(ExecuteReader(sql));
            CloseConn();
            return dts;
        }
        /// <summary>
        /// 把IDataReader对象数据导入到相同结构的DataTable对象。完成后会关闭vDataReader对象。
        /// </summary>
        /// <param name="vDataReader">要导出的IDataReader对象</param>
        /// <returns>包含所有IDataReader数据的DataTable数组</returns>
        public DataTable[] DataReaderToDataTables(IDataReader vDataReader)
        {
            ArrayList TableArrayList = new ArrayList();
            do
            {
                DataTable NewDataTable = new DataTable();
                DataTable TableSchema = vDataReader.GetSchemaTable();
                for (int i = 1; i <= TableSchema.Rows.Count; i++)
                    NewDataTable.Columns.Add(TableSchema.Rows[i - 1]["ColumnName"].ToString(), System.Type.GetType(TableSchema.Rows[i - 1]["DataType"].ToString()));
                while (vDataReader.Read() == true)
                {
                    DataRow NewDataRow = NewDataTable.NewRow();
                    for (int i = 1; i <= vDataReader.FieldCount; i++)
                        NewDataRow[i - 1] = vDataReader[i - 1];
                    NewDataTable.Rows.Add(NewDataRow);
                }
                TableArrayList.Add(NewDataTable);
            } while (vDataReader.NextResult() == true);
            DataTable[] ReturnDataTable = new DataTable[TableArrayList.Count];
            for (int i = 1; i <= TableArrayList.Count; i++)
                ReturnDataTable[i - 1] = (DataTable)TableArrayList[i - 1];

            //CloseConn(); //关闭数据库连接
            vDataReader.Close();
            return ReturnDataTable;
        }
        public SqlParameter CreateParameter()
        {
            return new SqlParameter();
        }
        public SqlCommand CreateCommand()
        {
            return new SqlCommand();
        }
        public SqlConnection CreateConnection()
        {
            CreateLink();
            return this.Conn;
        }
    }

    /// <summary>
    /// 数据表管理类
    /// </summary>
    public class DataTableManage
    {
        /// <summary>
        /// 要执行的Sql语句
        /// </summary>
        public string Sql
        {
            get { return mSql; }
            set
            {
                mSql = value;
                cmd.CommandText = mSql;
            }
        }
        private string mSql;
        private DBase dbconn;
        public SqlCommand cmd;


        public DataTableManage()
        {
            dbconn = new DBase();
            cmd = dbconn.CreateCommand();
            cmd.CommandTimeout = 600;
            cmd.Connection = dbconn.CreateConnection();
        }

        public DataTableManage(DBase conn)
        {
            dbconn = conn;
            cmd = dbconn.CreateCommand();
            cmd.CommandTimeout = 600;
            cmd.Connection = dbconn.CreateConnection();
        }

        public DataTableManage(string sql)
            : this()
        {
            this.Sql = sql;
        }

        public DataTableManage(DBase conn, string sql)
            : this(conn)
        {
            this.Sql = sql;
        }

        /// <summary>
        /// 增加int型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, int f_value)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Int;
            cmd.Parameters.Add(param);
            return param;
        }



        public DbParameter AddNewParam(string f_var, int f_value, ParameterDirection pd)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Int;
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }
        /// <summary>
        /// 增加string型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, string f_value)
        {
            if (f_value == null) f_value = "";
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            if (f_value.Length < 4000)
            {
                param.SqlDbType = SqlDbType.VarChar;
            }
            else
            {
                param.SqlDbType = SqlDbType.Text;
            }
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加string型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, string f_value, ParameterDirection pd)
        {
            if (f_value == null) f_value = "";
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            if (f_value.Length < 4000)
            {
                param.SqlDbType = SqlDbType.VarChar;
            }
            else
            {
                param.SqlDbType = SqlDbType.Text;
            }
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }


        /// <summary>
        /// 增加double型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, double f_value)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Float;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加double型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, double f_value, ParameterDirection pd)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Float;
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加decimal型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, decimal f_value)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.SmallMoney;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加decimal型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, decimal f_value, ParameterDirection pd)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.SmallMoney;
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加DateTime型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, DateTime f_value)
        {
            if (f_value == DateTime.MinValue) f_value = DateTime.Now;
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加DateTime型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, DateTime f_value, ParameterDirection pd)
        {
            if (f_value == DateTime.MinValue) f_value = DateTime.Now;
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.DateTime;
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加byte型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, bool f_value)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Bit;
            cmd.Parameters.Add(param);
            return param;
        }

        /// <summary>
        /// 增加byte型变量
        /// </summary>
        /// <param name="f_var">变量名</param>
        /// <param name="f_value">变量值</param>
        public DbParameter AddNewParam(string f_var, bool f_value, ParameterDirection pd)
        {
            SqlParameter param;
            param = dbconn.CreateParameter();
            param.ParameterName = f_var;
            param.Value = f_value;
            param.SqlDbType = SqlDbType.Bit;
            param.Direction = pd;
            cmd.Parameters.Add(param);
            return param;
        }


        /// <summary>
        /// 执行当前插入SQL语句，并获得@@IDENTITY值
        /// </summary>
        /// <returns>返回最近修改的主键值</returns>
        public int InsertGetId()
        {
            int DoID;
            cmd.ExecuteNonQuery();
            DoID = Convert.ToInt32(dbconn.ExecuteScalar("SELECT @@IDENTITY"));

            cmd.Dispose();
            dbconn.CloseConn();
            return DoID;
        }

        //<summary>
        //执行当前插入SQL语句
        //</summary>
        public void Insert()
        {
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            dbconn.CloseConn();

        }

        /// <summary>
        /// 执行当前更新语句，当Sql为更新语句时执行
        /// </summary>
        public void Update()
        {
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            dbconn.CloseConn();

        }


        /// <summary>
        /// 执行当前删除语句，当Sql为删除语句时执行
        /// </summary>
        public void Delete()
        {
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            dbconn.CloseConn();
        }

        /// <summary>
        /// 执行存储过程并返回数据表
        /// </summary>
        /// <returns>返回的数据表</returns>
        public DataTable Execute()
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = mSql;
            DataTable dt = dbconn.DataReaderToDataTables(cmd.ExecuteReader())[0];
            dbconn.CloseConn();
            return dt;
        }

        /// <summary>
        /// 执行存储过程，无返回值
        /// </summary>
        public void Exec()
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = mSql;
            cmd.ExecuteNonQuery();
            dbconn.CloseConn();
        }

        public object ExecuteScalarBySql()
        {
            cmd.CommandText = mSql;
            object obj = cmd.ExecuteScalar();
            dbconn.CloseConn();
            return obj;
        }
    }

}
